# -*- coding: utf-8 -*-

import sys
import os
import requests
import json
import time
import re
import traceback,threading
import MySQLdb
import datetime
import oss2

print('>>>正在初始化...')

DataBase = "sccplat"
SQL_CONNEC_URL = 'rm-bp1z598ttm5ahi9o15o.mysql.rds.aliyuncs.com'
SQL_USER_NAME = 'wangjun'
SQL_PWD = 'wj123456'
SQL_PORT = 3306
import time
need_borwer_info_list = []
class MainThread(threading.Thread):
    def __init__(self):
        threading.Thread.__init__(self)

    def run(self):
        
        while True:
            try:
                curr_time = datetime.datetime.now()
                print("任务线程在跑----",curr_time.strftime('%Y-%m-%d %H:%M:%S'))

                #更新project表中的公司名称和性别数量
                executetask()
            except Exception as e:
                print('登录主线程报错，继续执行。。。。')
                traceback.print_exc()
            
            time.sleep(150)

#照片存入oss
def upload_to_oss(url,path):
    response_data = {}
    response_data["issuc"]="false"
    try:
        # img_path = 'images/' + time.strftime('%Y-%m-%d-%H-%M---')
        # file_name = str(uuid.uuid4()) + '.jpg'
        # path = img_path + file_name
        auth = oss2.Auth('LTAI5t8ZgedKujGedvHwYfQ5', '6JOcP5Fakd1kEkNvdfvfSqlDy3LOTQ')
        bucket = oss2.Bucket(auth, 'http://oss-cn-shanghai.aliyuncs.com', 'ibuildingcloud-static')
        pic = requests.get(url)
        r = bucket.put_object(path, pic)
        if r.status == 200:
            return os.path.join('http://ibuildingcloud-static.oss-cn-shanghai.aliyuncs.com/', path)
        else:
            return
    except Exception as e: 
        traceback.print_exc()
        return

def selectFDb(table,**kwargs):
    results = None
    while True:
        try:
            db = MySQLdb.connect(SQL_CONNEC_URL, SQL_USER_NAME, SQL_PWD, DataBase,charset='utf8', port=SQL_PORT)
            cursor = db.cursor()
            break
        except Exception as e:
            print('%s' % e)
            time.sleep(5)

    update_list = []
    if table=='sjsso_projects':
        has_sql = '''SELECT code from {table}'''.format(table=table)
        cursor.execute(has_sql)
        results = []
        for field in cursor.fetchall():
            results.append(field[0])


    db.close()

    return results
def executetask():
    while True:
        try:
            db = MySQLdb.connect(SQL_CONNEC_URL, SQL_USER_NAME, SQL_PWD, DataBase,charset='utf8', port=SQL_PORT)
            cursor = db.cursor()
            break
        except Exception as e:
            print('%s' % e)
            time.sleep(5)

    select_projects_count_sql = '''select id from sjsso_projects'''
    project_counts = cursor.execute(select_projects_count_sql)

    select_crafy_count_sql = '''select id from sjsso_crafttype where parent_code is not null'''
    craft_counts = cursor.execute(select_crafy_count_sql)

    select_crafyanalyse_count_sql = '''SELECT id from sjsso_crafttype_analyse'''
    craftanalyse_counts = cursor.execute(select_crafyanalyse_count_sql)

    print('project_counts---',project_counts)
    print('craft_counts---',craft_counts)
    print('craftanalyse_counts---',craftanalyse_counts)
    if not craft_counts*project_counts==craftanalyse_counts:
        select_projects_sql = '''select code,name from sjsso_projects'''
        cursor.execute(select_projects_sql)
        for item in cursor.fetchall():
            select_crafttype_sql = '''select code,name,parent_code,parent_name from sjsso_crafttype where parent_code is not null'''
            cursor.execute(select_crafttype_sql)
            for crafitem in cursor.fetchall():
                crafytype2_analyse_sql = '''SELECT id from sjsso_crafttype_analyse where  code="{PhidCrafttype_code}" and project_code="{project_code}"'''.format(PhidCrafttype_code=crafitem[0],project_code=item[0])
                crafytype2_analyse_count = cursor.execute(crafytype2_analyse_sql)
                print('crafytype2_analyse_count----',crafytype2_analyse_count)
                if crafytype2_analyse_count==0:
                    print('添加---')
                    crafytype2_analyse_insert_sql = '''INSERT ignore INTO sjsso_crafttype_analyse(code,name,parent_code,parent_name,project_code,project_name) VALUES("{code}","{name}","{parent_code}","{parent_name}","{project_code}","{project_name}")'''.format(code=crafitem[0],name=crafitem[1],parent_code=crafitem[2],parent_name=crafitem[3],project_code=item[0],project_name=item[1])
                    cursor.execute(crafytype2_analyse_insert_sql)
                    db.commit()

    
    #更新project表中的公司名称和性别数量
    update_project_sql = '''update sjsso_projects p join sjsso_companys c on p.company=c.orid set p.company_name=c.name where p.company_name is null'''
    cursor.execute(update_project_sql)
    db.commit()
    update_project_malenum_sql = '''update sjsso_projects p set p.male_num = (select w.counts from (select count(id) counts,project_code from sjsso_worker where Sex='男' group by project_code) w where w.project_code=p.code)'''
    cursor.execute(update_project_malenum_sql)
    db.commit()
    update_project_femalenum_sql = '''update sjsso_projects p set p.record_time=now(),p.female_num = (select w.counts from (select count(id) counts,project_code from sjsso_worker where Sex='女' group by project_code) w where w.project_code=p.code)'''
    cursor.execute(update_project_femalenum_sql)
    db.commit()
    
    #更新工种数量统计
    update_crafftype_num_sql = '''update sjsso_crafttype p set p.num = (select w.counts from (select count(id) counts,PhidCrafttype from sjsso_worker group by PhidCrafttype) w where w.PhidCrafttype=p.code)'''
    cursor.execute(update_crafftype_num_sql)
    db.commit()
    #更新班组数量统计
    update_crafftype_num_sql = '''update sjsso_teams p set p.num = (select w.counts from (select count(id) counts,PhidTeamsgrId from sjsso_worker group by PhidTeamsgrId) w where w.PhidTeamsgrId=p.code)'''
    cursor.execute(update_crafftype_num_sql)
    db.commit()


    #更新用户图片
    select_worker_pic_sql = '''select yg_Id,id,RecentPic,CertPic from sjsso_worker where oss_RecentPic is null limit 100'''
    select_worker_pic_count = cursor.execute(select_worker_pic_sql)
    if select_worker_pic_count>0:
        for item in cursor.fetchall():
            id = item[1]
            yg_Id = item[0]
            RecentPic = item[2]
            CertPic = item[3]
            oss_RecentPic = upload_to_oss(RecentPic,'facepic/'+item[0]+'_RecentPic.jpg')
            if oss_RecentPic:
                update_recentpic_sql = '''update sjsso_worker set oss_RecentPic="{pic}" where id={id}'''.format(pic=oss_RecentPic,id=id)
                cursor.execute(update_recentpic_sql)
                db.commit()
            oss_CertPic = upload_to_oss(CertPic,'facepic/'+item[0]+'_CertPic.jpg')
            if oss_CertPic:
                update_certpic_sql = '''update sjsso_worker set oss_CertPic="{pic}" where id={id}'''.format(pic=oss_CertPic,id=id)
                cursor.execute(update_certpic_sql)
                db.commit()

            # print(oss_RecentPic)

    #更新填充用户信息
    update_worker_project_name_sql = '''update sjsso_worker p set p.project_name = (select w.name from sjsso_projects w where w.code=p.project_code) where p.project_name is null'''
    cursor.execute(update_worker_project_name_sql)
    db.commit()
    update_worker_teamname_sql = '''update sjsso_worker p set p.PhidTeamsgrId_name = (select w.name from sjsso_teams w where w.code=p.PhidTeamsgrId) where p.PhidTeamsgrId_name is null'''
    cursor.execute(update_worker_teamname_sql)
    db.commit()
    update_worker_worktype_sql = '''update sjsso_worker p set p.PhidWorktype_name = (select w.name from sjsso_crafttype w where w.code=p.PhidWorktype) where p.PhidWorktype_name is null'''
    cursor.execute(update_worker_worktype_sql)
    db.commit()
    update_worker_work_sql = '''update sjsso_worker p set p.PhidCrafttype_name = (select w.name from sjsso_crafttype w where w.code=p.PhidCrafttype) where p.PhidCrafttype_name is null'''
    cursor.execute(update_worker_work_sql)
    db.commit()
    update_worker_company_sql = '''update sjsso_worker p set p.PhidCraftComp_name = (select w.name from sjsso_labour_company w where w.code=p.PhidCraftComp) where p.PhidCraftComp_name is null'''
    cursor.execute(update_worker_company_sql)
    db.commit()

    #出入记录更新
    update_workerrecord_team_sql = '''update sjsso_worker_records p set p.PhidTeamsgrId = (select w.PhidTeamsgrId from sjsso_worker w where w.PhId=p.PhId) where p.PhidTeamsgrId is null'''
    cursor.execute(update_workerrecord_team_sql)
    db.commit()
    update_workerrecord_teamname_sql = '''update sjsso_worker_records p set p.PhidTeamsgrId_name = (select w.PhidTeamsgrId_name from sjsso_worker w where w.PhId=p.PhId) where p.PhidTeamsgrId_name is null'''
    cursor.execute(update_workerrecord_teamname_sql)
    db.commit()

    update_workerrecord_project_name_sql = '''update sjsso_worker_records p set p.project_name = (select w.name from sjsso_projects w where w.code=p.project_code) where p.project_name is null'''
    cursor.execute(update_workerrecord_project_name_sql)
    db.commit()
    update_workerrecord_worktype_sql = '''update sjsso_worker_records p set p.PhidWorktype_name = (select w.name from sjsso_crafttype w where w.code=p.PhidWorktype) where p.PhidWorktype_name is null'''
    cursor.execute(update_workerrecord_worktype_sql)
    db.commit()
    update_workerrecord_work_sql = '''update sjsso_worker_records p set p.Crafttype_name = (select w.name from sjsso_crafttype w where w.code=p.Crafttype) where p.Crafttype_name is null'''
    cursor.execute(update_workerrecord_work_sql)
    db.commit()

    #劳务公司
    update_labourcompany_project_name_sql = '''update sjsso_labour_company  set projectname=name where projectname is null'''
    cursor.execute(update_labourcompany_project_name_sql)
    db.commit()

    today_time = datetime.datetime.now()
    today_time = today_time.strftime('%Y-%m-%d')+" 00:00:00"

    update_labourcompany_regnum_sql = '''update sjsso_labour_company p set p.reg_num = (select w.counts from (select count(id) counts,PhidCraftComp from sjsso_worker group by PhidCraftComp) w where w.PhidCraftComp=p.code)'''
    cursor.execute(update_labourcompany_regnum_sql)
    db.commit()

    update_labourcompany_num_sql = '''update sjsso_labour_company p set record_time=now(),p.num = (select w.counts from (select count(id) counts,PhidCompane1 from sjsso_worker_records where today="'''+today_time+'''" and (InBuildDt!='0000-00-00 00:00:00' and OutBuildDt='0000-00-00 00:00:00') group by PhidCompane1) w where w.PhidCompane1=p.code)'''
    cursor.execute(update_labourcompany_num_sql)
    db.commit()

    #工种表
    update_crafttype_regnum_sql = '''update sjsso_crafttype p set p.reg_num = (select w.counts from (select count(id) counts,PhidCrafttype from sjsso_worker group by PhidCrafttype) w where w.PhidCrafttype=p.code)'''
    cursor.execute(update_crafttype_regnum_sql)
    db.commit()

    update_crafttype_parentname_sql = '''update sjsso_crafttype p set p.parent_name = (select w.name from (select name,code from sjsso_crafttype where parent_code is null group by name) w where w.code=p.parent_code)'''
    cursor.execute(update_crafttype_parentname_sql)
    db.commit()

    update_crafttype_num_sql = '''update sjsso_crafttype p set p.record_time=now(),p.num = (select w.counts from (select count(id) counts,Crafttype from sjsso_worker_records where today="'''+today_time+'''" and (InBuildDt!='0000-00-00 00:00:00' and OutBuildDt='0000-00-00 00:00:00') group by Crafttype) w where w.Crafttype=p.name)'''
    cursor.execute(update_crafttype_num_sql)
    db.commit()

    #班组表
    update_sjsso_teams_projectname_sql = '''update sjsso_teams p set p.projectname = (select w.project_name from (select project_name,PhidTeamsgrId from sjsso_worker group by PhidTeamsgrId) w where w.PhidTeamsgrId=p.code)'''
    cursor.execute(update_sjsso_teams_projectname_sql)
    db.commit()

    update_sjsso_teams_regnum_sql = '''update sjsso_teams p set p.reg_num = (select w.counts from (select count(id) counts,PhidTeamsgrId from sjsso_worker group by PhidTeamsgrId) w where w.PhidTeamsgrId=p.code)'''
    cursor.execute(update_sjsso_teams_regnum_sql)
    db.commit()


    update_sjsso_teams_num_sql = '''update sjsso_teams p set p.record_time=now(),p.num = (select w.counts from (select count(id) counts,PhidTeamsgrId from sjsso_worker_records where today="'''+today_time+'''" and (InBuildDt!='0000-00-00 00:00:00' and OutBuildDt='0000-00-00 00:00:00') group by PhidTeamsgrId) w where w.PhidTeamsgrId=p.code)'''
    cursor.execute(update_sjsso_teams_num_sql)
    db.commit()

    #工种分析表
    update_crafttypeanalyse_projectname_sql = '''update sjsso_crafttype_analyse p set p.project_name = (select w.name from sjsso_projects w where w.code=p.project_code) where p.project_name is null'''
    cursor.execute(update_crafttypeanalyse_projectname_sql)
    db.commit()

    update_crafttype_analyse_regnum_sql = '''update sjsso_crafttype_analyse p set p.reg_num = (select w.counts from (select count(id) counts,PhidCrafttype,project_code from sjsso_worker group by PhidCrafttype,project_code) w where w.PhidCrafttype=p.code and p.project_code=w.project_code)'''
    cursor.execute(update_crafttype_analyse_regnum_sql)
    db.commit()

    update_crafttype_num_sql = '''update sjsso_crafttype_analyse p set p.record_time=now(),p.num = (select w.counts from (select count(id) counts,Crafttype,project_code from sjsso_worker_records where today="'''+today_time+'''" and (InBuildDt!='0000-00-00 00:00:00' and OutBuildDt='0000-00-00 00:00:00') group by Crafttype,project_code) w where w.Crafttype=p.name and p.project_code=w.project_code)'''
    cursor.execute(update_crafttype_num_sql)
    db.commit()

    db.close()

import configparser
if __name__ == '__main__':
    print(">>>>>>>>  执行更新任务")
    
    MainThread().start()
    print('执行成功')
    time.sleep(2)

    
    # try:
    #     browser = None
    #     profile=webdriver.FirefoxProfile()
    #     profile.accept_untrusted_certs=True
    #     geckodriverPath = os.path.join(proDir, "geckodriver")#将驱动放在同目录下
    #     browser=webdriver.Firefox(firefox_profile=profile,executable_path=geckodriverPath) #windows版
    #     loginurl('dd651','Xrj888888',60,browser)
    # except Exception as e:
    #     print('登录主线程报错，继续执行。。。。')
    #     traceback.print_exc()

